﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.DLR;
using PdfLib;

namespace Workflows.Components.TextMining.EnumerationObjects
{
    /// <summary>
    /// 
    /// </summary>
    //public class ExtractionWorker : IWorker
    //{
    //    private Thread workerThread;
    //    private ExtractionInstruction instruction;
    //    private ExtractionOutput output;
    //    private RunContext _Context;
    //    private Guid jobTicketID;

    //    // rules
    //    private ExtractionRule _Rule;

    //    #region IWorker Members
    //    /// <summary></summary>
    //    public Thread WorkerThread
    //    {
    //        get { return workerThread; }
    //    }
    //    /// <summary></summary>
    //    public InstructionBase Instruction
    //    {
    //        get { return instruction; }
    //    }
    //    /// <summary></summary>
    //    public ResultBase Output
    //    {
    //        get { return output; }
    //    }
    //    /// <summary></summary>
    //    public Type ComponentType
    //    {
    //        get { return typeof(ExtractionComponent); }
    //    }
    //    /// <summary></summary>
    //    public RunContext Context
    //    {
    //        get { return _Context; }
    //        set { _Context = value; }
    //    }

    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    /// <param name="ticketGuid"></param>
    //    /// <param name="request"></param>
    //    public void SetInstruction(Guid ticketGuid, InstructionBase request)
    //    {
    //        this.jobTicketID = ticketGuid;
    //        this.instruction = (ExtractionInstruction)request;
    //    }
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    /// <param name="context"></param>
    //    /// <param name="ticketGuid"></param>
    //    /// <param name="request"></param>
    //    public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request)
    //    {
    //        this.jobTicketID = ticketGuid;
    //        this.instruction = (ExtractionInstruction)request;
    //        this._Context = context;
    //    }

    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    public void DoWork()
    //    {
    //        if (this.WorkerStartedEvent != null)
    //            this.WorkerStartedEvent(this.jobTicketID, this, this.GetType(), this.instruction);
    //        this.workerThread = Thread.CurrentThread;
    //        this.RunExtraction();
    //    }
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    public void DoWorkAsync()
    //    {
    //        if (this.WorkerStartedEvent != null)
    //            this.WorkerStartedEvent(this.jobTicketID, this, this.GetType(), this.instruction);
    //        this.workerThread = new Thread(new ThreadStart(this.RunExtraction));
    //        this.workerThread.Start();
    //    }
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    public void CancelWork()
    //    {
    //        if (this.workerThread != null && this.workerThread.IsAlive)
    //        {
    //            this.workerThread.Abort();
    //            if (this.WorkerCancelledEvent != null)
    //            {
    //                this.WorkerCancelledEvent(this.jobTicketID, this.GetType(), this.instruction);
    //            }
    //        }
    //    }

    //    public event WorkerStarted WorkerStartedEvent;
    //    public event WorkerFinished WorkerFinishedEvent;
    //    public event WorkerHasError WorkerErredEvent;
    //    public event WorkerCancelled WorkerCancelledEvent;
    //    public event WorkerStatusChanged WorkerStatusChangedEvent;

    //    #endregion

    //    #region test
    //    public Dictionary<int, List<DocumentPersonRecord>> TestTableDataExtractionInstruction(string filePath)
    //    {
    //        this._Rule = ExtractionRule.ReadRuleFromXmlFile();
    //        bool isEsnap = false;
    //        bool isPhs2590 = false;
    //        Dictionary<int, List<DocumentPersonRecord>> personRecordsByPages = new Dictionary<int, List<DocumentPersonRecord>>();

    //        try
    //        {
    //            FileTypeDetector fileTypeDetector = new FileTypeDetector();
    //            EsnapExtractor esnapExtractor = new EsnapExtractor();
    //            esnapExtractor.OnExtractionError += new ExtractionError(esnapExtractor_OnExtractionError);
    //            Phs2590Extractor phsExtractor = new Phs2590Extractor();
    //            phsExtractor.OnExtractionError += new ExtractionError(phsExtractor_OnExtractionError);
    //            List<int> pageNumbers = fileTypeDetector.DetermineFileTypes(this._Rule, filePath, ref isEsnap, ref isPhs2590);
    //            if (isEsnap)
    //            {
    //                // Step 5a: esnap extraction
    //                Dictionary<int, DataTable> tableDataByPage = esnapExtractor.GetTableFromFile(filePath, this._Rule, pageNumbers);
    //                personRecordsByPages = this.GetPersonRecords(tableDataByPage, this._Rule);
    //            }
    //            else if (isPhs2590)
    //            {
    //                // Step 5b: phs2590 extraction
    //                Dictionary<int, DataTable> tableDataByPage = phsExtractor.GetDataFromFile(filePath,
    //                                                                                          this._Rule,
    //                                                                                          pageNumbers);
    //                personRecordsByPages = this.GetPersonRecords(tableDataByPage, this._Rule);
    //            }
    //            else
    //            {
    //                throw new Exception("Unable to detect table");
    //            }
    //        }
    //        catch (Exception ex)
    //        {
    //            throw new Exception(ex.ToString());
    //        }
    //        return personRecordsByPages;
    //    }
    //    #endregion

    //    #region extraction
    //    private void RunExtraction()
    //    {
    //        // initialize
    //        this._Rule = ExtractionRule.ReadRuleFromXmlFile();
    //        int fileScanned = 0;
    //        int fileProcessed = 0;
    //        int personExtracted = 0;
    //        string appFolderPath =
    //            Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Workflow Data");
    //        if (!Directory.Exists(appFolderPath))
    //        {
    //            Directory.CreateDirectory(appFolderPath);
    //        }
    //        string logFilePath = this.instruction.LogFilePath;
    //        if (string.IsNullOrEmpty(logFilePath))
    //        {
    //            logFilePath = Path.Combine(appFolderPath, "EnumerationExtraction.log");
    //        }
    //        if (File.Exists(logFilePath))
    //        {
    //            File.Delete(logFilePath);
    //        }
    //        string errFilePath = this.instruction.ErrorFilePath;
    //        if (string.IsNullOrEmpty(errFilePath))
    //        {
    //            errFilePath = Path.Combine(appFolderPath, "EnumeraionExtraction.err");
    //        }
    //        if (File.Exists(errFilePath))
    //        {
    //            File.Delete(errFilePath);
    //        }
    //        StreamWriter logWriter = new StreamWriter(logFilePath);
    //        StreamWriter errWriter = new StreamWriter(errFilePath);
    //        EsnapExtractor esnapExtractor = new EsnapExtractor();
    //        esnapExtractor.OnExtractionError += new ExtractionError(esnapExtractor_OnExtractionError);
    //        Phs2590Extractor phsExtractor = new Phs2590Extractor();
    //        phsExtractor.OnExtractionError += new ExtractionError(phsExtractor_OnExtractionError);
    //        // run
    //        string connStr = ConfigurationManager.ConnectionStrings["EnumerationData"].ConnectionString;
    //        ORMapping<DocumentExtraction> ormDocEx = new ORMapping<DocumentExtraction>();
    //        ORMapping<DocumentPersonRecord> ormPersonRec = new ORMapping<DocumentPersonRecord>();
    //        DocumentExtractionRun run = new DocumentExtractionRun();
    //        run.RunID = this.instruction.RunID;
    //        run.FiscalYears = instruction.FiscalYears;
    //        run.ActivityCodes = this.instruction.ActivityCodes;
    //        run.ApplTypeCodes = this.instruction.ApplTypeCodes;
    //        run.DocTypeCodes = this.instruction.DocTypeCodes;
    //        run.FileScanned = fileScanned;
    //        run.FileProcessed = fileProcessed;
    //        run.PersonExtracted = personExtracted;
    //        run.StartTime = DateTime.Now;
    //        run.FinishTime = TimeUtil.DbMaxTime;
    //        run.Workstation = Environment.MachineName;
    //        run.OperatorName = Environment.UserName;
    //        ORMapping<DocumentExtractionRun> ormRun = new ORMapping<DocumentExtractionRun>();
    //        ormRun.Update(connStr, ref run);
    //        this.instruction.RunID = run.RunID;

    //        try
    //        {
    //            // step 1: loop through fiscal years
    //            foreach (int fy in run.FiscalYears)
    //            {
    //                try
    //                {
    //                    // step 2: get pdf docs
    //                    Dictionary<string, PdfDocument> pdfDocs =
    //                        this.GetPdfDocs(
    //                            fy, this.instruction.ActivityCodes,
    //                            this.instruction.ApplTypeCodes,
    //                            this.instruction.DocTypeCodes);
    //                    if (pdfDocs == null || pdfDocs.Count == 0)
    //                        throw new Exception("Unable to find pdf docs for FY " + fy);

    //                    // step 3: loop through file 
    //                    FileTypeDetector fileTypeDetector = new FileTypeDetector();
    //                    foreach (string fileName in pdfDocs.Keys)
    //                    {
    //                        PdfDocument pdfDoc = pdfDocs[fileName];
    //                        fileScanned++;
    //                        try
    //                        {
    //                            bool isEsnap = false;
    //                            bool isPhs2590 = false;
    //                            DocumentExtraction docExtraction = new DocumentExtraction();
    //                            docExtraction.ApplID = pdfDoc.ApplID;
    //                            docExtraction.DocID = pdfDoc.DocID;
    //                            docExtraction.DocumentExtractionID = -1;
    //                            docExtraction.IsEsnap = isEsnap;
    //                            docExtraction.IsPhs2590 = isPhs2590;
    //                            docExtraction.RunID = this.instruction.RunID;
    //                            docExtraction.Status = ExtractionStatus.NotDetermined;
    //                            docExtraction.PageNumbers = null;
    //                            docExtraction.PersonCount = 0;

    //                            // Step 4: determine file type
    //                            List<int> pageNumbers = fileTypeDetector.DetermineFileTypes(this._Rule, pdfDoc.FilePath, ref isEsnap, ref isPhs2590);
    //                            Dictionary<int, List<DocumentPersonRecord>> personRecordsByPages = new Dictionary<int, List<DocumentPersonRecord>>();
    //                            if (isEsnap)
    //                            {
    //                                // Step 5a: esnap extraction
    //                                docExtraction.IsEsnap = true;
    //                                Dictionary<int, DataTable> tableDataByPage = esnapExtractor.GetTableFromFile(pdfDoc.FilePath, this._Rule, pageNumbers);
    //                                personRecordsByPages = this.GetPersonRecords(tableDataByPage, this._Rule);
    //                                if (personRecordsByPages != null && personRecordsByPages.Count > 0)
    //                                {
    //                                    List<int> pageNums = new List<int>();
    //                                    foreach (int pageNum in personRecordsByPages.Keys)
    //                                    {
    //                                        pageNums.Add(pageNum);
    //                                        docExtraction.PersonCount += personRecordsByPages[pageNum].Count;
    //                                    }
    //                                    docExtraction.PageNumbers = pageNums.ToArray();
    //                                    docExtraction.Status = ExtractionStatus.ExtractionSucceed;
    //                                }
    //                                else
    //                                {
    //                                    docExtraction.Status = ExtractionStatus.ExtractionFailed;
    //                                }
    //                            }
    //                            else if (isPhs2590)
    //                            {
    //                                // Step 5b: phs2590 extraction
    //                                docExtraction.IsPhs2590 = true;
    //                                Dictionary<int, DataTable> tableDataByPage = phsExtractor.GetDataFromFile(pdfDoc.FilePath,
    //                                                                                                          this._Rule,
    //                                                                                                          pageNumbers);
    //                                personRecordsByPages = this.GetPersonRecords(tableDataByPage, this._Rule);
    //                                if (personRecordsByPages != null && personRecordsByPages.Count > 0)
    //                                {
    //                                    List<int> pageNums = new List<int>();
    //                                    foreach (int pageNum in personRecordsByPages.Keys)
    //                                    {
    //                                        pageNums.Add(pageNum);
    //                                        docExtraction.PersonCount += personRecordsByPages[pageNum].Count;
    //                                    }
    //                                    docExtraction.PageNumbers = pageNums.ToArray();
    //                                    docExtraction.Status = ExtractionStatus.ExtractionSucceed;
    //                                }
    //                                else
    //                                {
    //                                    docExtraction.Status = ExtractionStatus.ExtractionFailed;
    //                                }
    //                            }
    //                            else
    //                            {
    //                                docExtraction.Status = ExtractionStatus.UnableToFindTable;
    //                            }

    //                            // Step 6: save to DB
    //                            ormDocEx.Update(connStr, ref docExtraction);
    //                            if (personRecordsByPages != null && personRecordsByPages.Count > 0)
    //                            {
    //                                foreach (int pageNum in personRecordsByPages.Keys)
    //                                {
    //                                    List<DocumentPersonRecord> personRecs = personRecordsByPages[pageNum];
    //                                    for (int i = 0; i < personRecs.Count; i++)
    //                                    {
    //                                        DocumentPersonRecord personRec = personRecs[i];
    //                                        personRec.DocExtractionID = docExtraction.DocumentExtractionID;
    //                                        personRec.DocumentpersonRecordID = -1;
    //                                        ormPersonRec.Update(connStr, ref personRec);
    //                                    }
    //                                }
    //                            }
    //                            fileProcessed++;
    //                            personExtracted += docExtraction.PersonCount;
    //                            logWriter.WriteLine("{0}: {1}-{2}, person count: {3}", DateTime.Now.ToString(), fileName,
    //                                                docExtraction.Status.ToString(), docExtraction.PersonCount);
    //                            logWriter.Flush();
    //                            if (this.WorkerStatusChangedEvent != null)
    //                            {
    //                                int percent = fileScanned * 100 / pdfDocs.Count;
    //                                string msg =
    //                                    string.Format(
    //                                        "FY {0}: Scanning {1} of {2} files \"{3}\": " +
    //                                        "processed {4} files, extracted {5} persons",
    //                                        fy, fileScanned, pdfDocs.Count, fileName,
    //                                        fileProcessed, personExtracted);
    //                                this.WorkerStatusChangedEvent(this.jobTicketID, this.GetType(), this.instruction, msg,
    //                                                              percent);
    //                            }
    //                        }
    //                        catch (Exception ex1)
    //                        {
    //                            if (this.WorkerErredEvent != null)
    //                            {
    //                                this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, ex1.ToString());
    //                                errWriter.WriteLine("{0} {1}: {2}", DateTime.Now.ToString(), pdfDoc.FilePath, ex1.ToString());
    //                                errWriter.Flush();
    //                            }
    //                        }
    //                    }

    //                    // update run 
    //                    run.FinishTime = DateTime.Now;
    //                    run.FileScanned = fileScanned;
    //                    run.FileProcessed = fileProcessed;
    //                    run.PersonExtracted = personExtracted;
    //                    ormRun.Update(connStr, ref run);
    //                }
    //                catch (Exception fyEx)
    //                {
    //                    if (this.WorkerErredEvent != null)
    //                    {
    //                        this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, "Error while extracting docs for fy " + fy + ": " + fyEx.ToString());
    //                    }
    //                }
    //            }

    //            // Step 1: get pdf docs


    //            // Step 2: loop 


    //            this.output = new ExtractionOutput(this.instruction.OwnerComponentID, this.jobTicketID, fileScanned,
    //                                               fileProcessed, personExtracted, logFilePath, errFilePath);
    //            if (this.WorkerFinishedEvent != null)
    //            {
    //                this.WorkerFinishedEvent(this.jobTicketID, this.GetType(), this.instruction, this.output);
    //            }
    //        }
    //        catch (Exception ex)
    //        {
    //            if (this.WorkerErredEvent != null)
    //            {
    //                this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, ex.ToString());
    //            }
    //        }
    //        finally
    //        {
    //            logWriter.Flush();
    //            logWriter.Close();
    //            errWriter.Flush();
    //            errWriter.Close();
    //        }
    //    }

    //    void phsExtractor_OnExtractionError(ExtractionStatus status, string error)
    //    {
    //        if (this.WorkerErredEvent != null)
    //        {
    //            Trace.WriteLine(error);
    //            // this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, error);
    //        }
    //    }

    //    void esnapExtractor_OnExtractionError(ExtractionStatus status, string error)
    //    {
    //        if (this.WorkerErredEvent != null)
    //        {
    //            Trace.WriteLine(error);
    //            // this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, error);
    //        }
    //    }

    //    private Dictionary<int, List<DocumentPersonRecord>> GetPersonRecords(Dictionary<int, DataTable> tableDataByPage, ExtractionRule rule)
    //    {
    //        Dictionary<int, List<DocumentPersonRecord>> personRecsByPage = new Dictionary<int, List<DocumentPersonRecord>>();
    //        foreach (int pageNum in tableDataByPage.Keys)
    //        {
    //            List<DocumentPersonRecord> personRecs = new List<DocumentPersonRecord>();
    //            DataTable dtPersonRec = tableDataByPage[pageNum];
    //            Dictionary<string, string> colName_Field_Mappings = new Dictionary<string, string>();
    //            for (int i = 0; i < dtPersonRec.Columns.Count; i++)
    //            {
    //                string colName = dtPersonRec.Columns[i].ColumnName;
    //                foreach (int applyOrder in rule.ColHeaderOptions.Keys)
    //                {
    //                    List<PdfTableColumnHeaderMapping> colHeaderMaps = rule.ColHeaderOptions[applyOrder];
    //                    foreach (PdfTableColumnHeaderMapping colHeaderMap in colHeaderMaps)
    //                    {
    //                        if (colHeaderMap.FieldNameInPdf.ToLower() == colName.ToLower())
    //                        {
    //                            if (!colName_Field_Mappings.ContainsKey(colName.ToLower()))
    //                            {
    //                                colName_Field_Mappings.Add(colName.ToLower(), colHeaderMap.FieldNameInDB);
    //                            }
    //                            break;
    //                        }
    //                    }
    //                    if (colName_Field_Mappings.ContainsKey(colName.ToLower()))
    //                    {
    //                        break;
    //                    }
    //                }
    //            }
    //            foreach (DataRow dr in dtPersonRec.Rows)
    //            {
    //                DocumentPersonRecord personRec = new DocumentPersonRecord();
    //                for (int i = 0; i < dtPersonRec.Columns.Count; i++)
    //                {
    //                    if (colName_Field_Mappings.ContainsKey(dtPersonRec.Columns[i].ColumnName.ToLower()))
    //                    {
    //                        if (dr[i] != null && dr[i] != DBNull.Value)
    //                        {
    //                            string memberName = colName_Field_Mappings[dtPersonRec.Columns[i].ColumnName.ToLower()];
    //                            MemberInfo[] members = typeof(DocumentPersonRecord).GetMember(memberName);
    //                            if (members != null && members.Length == 1)
    //                            {
    //                                MemberInfo member = members[0];
    //                                if (member.MemberType == MemberTypes.Field)
    //                                {
    //                                    ((FieldInfo)member).SetValue(personRec, dr[i]);
    //                                }
    //                                else if (member.MemberType == MemberTypes.Property)
    //                                {
    //                                    ((PropertyInfo)member).SetValue(personRec, dr[i], null);
    //                                }
    //                            }
    //                        }
    //                    }
    //                }
    //                personRec.PageNumber = pageNum;
    //                personRecs.Add(personRec);
    //            }
    //            personRecsByPage.Add(pageNum, personRecs);
    //        }
    //        return personRecsByPage;
    //    }
    //    #endregion

    //    #region retrieve docs
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    /// <param name="fy"></param>
    //    /// <param name="activityCodes"></param>
    //    /// <param name="applTypeCodes"></param>
    //    /// <param name="docTypeCodes"></param>
    //    /// <returns></returns>
    //    private Dictionary<string, PdfDocument> GetPdfDocs(int fy, string[] activityCodes, string[] applTypeCodes, string[] docTypeCodes)
    //    {
    //        Dictionary<string, PdfDocument> pdfDocs = new Dictionary<string, PdfDocument>();
    //        string connStr = ConfigurationManager.ConnectionStrings["Enumeration"].ConnectionString;
    //        SqlConnection conn = new SqlConnection(connStr);
    //        string localDocRootFolderPath = this.instruction.LocalDocRootFolderPath;
    //        if (ConfigurationManager.AppSettings.Get("DocRootFolder") != null)
    //        {
    //            localDocRootFolderPath = ConfigurationManager.AppSettings.Get("DocRootFolder");
    //        }
    //        Dictionary<string, string> fileName_Path_Mappings = new Dictionary<string, string>();
    //        if (Directory.Exists(localDocRootFolderPath))
    //        {
    //            DirectoryInfo rootDirInfo = new DirectoryInfo(localDocRootFolderPath);
    //            FileInfo[] pdfFileInfos = rootDirInfo.GetFiles("*.pdf", SearchOption.AllDirectories);
    //            foreach (FileInfo pdfFileInfo in pdfFileInfos)
    //            {
    //                if (!fileName_Path_Mappings.ContainsKey(pdfFileInfo.Name.ToUpper()))
    //                {
    //                    fileName_Path_Mappings.Add(pdfFileInfo.Name.ToUpper(), pdfFileInfo.FullName);
    //                }
    //            }
    //        }

    //        try
    //        {
    //            conn.Open();
    //            SqlCommand cmd = conn.CreateCommand();
    //            cmd.CommandText =
    //                "select Appl_ID, " +
    //                "Doc_ID, " +
    //                "Activity_Code, " +
    //                "Appl_Type_Code, " +
    //                "Doc_Type_Code, " +
    //                "FY," +
    //                "Doc_File_Name " +
    //                "from Docs_T " +
    //                "where FY=" + fy;
    //            if (activityCodes != null && activityCodes.Length > 0)
    //            {
    //                string whereSql1 = " and Activity_Code in (";
    //                for (int i = 0; i < activityCodes.Length; i++)
    //                {
    //                    whereSql1 += "'" + activityCodes[i] + "'";
    //                    if (i < activityCodes.Length - 1)
    //                    {
    //                        whereSql1 += ",";
    //                    }
    //                }
    //                whereSql1 += ")";
    //                cmd.CommandText += whereSql1;
    //            }
    //            if (applTypeCodes != null && applTypeCodes.Length > 0)
    //            {
    //                string whereSql2 = " and Appl_Type_Code in {";
    //                for (int i = 0; i < applTypeCodes.Length; i++)
    //                {
    //                    whereSql2 += "'" + applTypeCodes[i] + "'";
    //                    if (i < applTypeCodes.Length - 1)
    //                    {
    //                        whereSql2 += ",";
    //                    }
    //                }
    //                whereSql2 += ")";
    //                cmd.CommandText += whereSql2;
    //            }
    //            if (docTypeCodes != null && docTypeCodes.Length > 0)
    //            {
    //                string whereSql3 = " and Doc_Type_Code in (";
    //                for (int i = 0; i < docTypeCodes.Length; i++)
    //                {
    //                    whereSql3 += "'" + docTypeCodes[i] + "'";
    //                    if (i < docTypeCodes.Length - 1)
    //                    {
    //                        whereSql3 += ",";
    //                    }
    //                }
    //                whereSql3 += ")";
    //                cmd.CommandText += whereSql3;
    //            }

    //            SqlDataReader reader = cmd.ExecuteReader();
    //            while (reader.Read())
    //            {
    //                PdfDocument pdfDoc = new PdfDocument();
    //                pdfDoc.ApplID = int.Parse(reader["Appl_ID"].ToString());
    //                pdfDoc.DocID = int.Parse(reader["Doc_ID"].ToString());
    //                pdfDoc.ActivityCode = reader["Activity_Code"].ToString();
    //                pdfDoc.ApplTypeCode = reader["Appl_Type_Code"].ToString();
    //                pdfDoc.DocTypeCode = reader["Doc_Type_Code"].ToString();
    //                pdfDoc.FY = int.Parse(reader["FY"].ToString());
    //                pdfDoc.FileName = reader["Doc_File_Name"].ToString();
    //                if (fileName_Path_Mappings.ContainsKey(pdfDoc.FileName.ToUpper()))
    //                {
    //                    pdfDoc.FilePath = fileName_Path_Mappings[pdfDoc.FileName.ToUpper()];
    //                }
    //                else
    //                {
    //                    pdfDoc.FilePath = string.Empty;
    //                }

    //                pdfDocs.Add(pdfDoc.FileName, pdfDoc);
    //            }
    //            reader.Close();

    //            foreach (PdfDocument pdfDoc in pdfDocs.Values)
    //            {
    //                if (string.IsNullOrEmpty(pdfDoc.FilePath))
    //                {
    //                    cmd.CommandText = "select Doc_BFile from Docs_T where Appl_ID=" + pdfDoc.ApplID;
    //                    try
    //                    {
    //                        if (conn.State != ConnectionState.Open)
    //                        {
    //                            conn.Open();
    //                        }
    //                        reader = cmd.ExecuteReader();
    //                        if (reader.Read())
    //                        {
    //                            byte[] contents = (byte[])reader["Doc_BFile"];
    //                            string filePath = this.SaveFile(contents, localDocRootFolderPath, pdfDoc.FileName);
    //                            pdfDoc.FilePath = filePath;
    //                        }
    //                        reader.Close();
    //                    }
    //                    catch (Exception ex1)
    //                    {
    //                        if (this.WorkerErredEvent != null)
    //                        {
    //                            this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, ex1.ToString());
    //                        }
    //                        if (!reader.IsClosed)
    //                        {
    //                            reader.Close();
    //                        }
    //                    }
    //                }
    //            }
    //        }
    //        catch (Exception ex)
    //        {
    //            if (this.WorkerErredEvent != null)
    //            {
    //                this.WorkerErredEvent(this.jobTicketID, this.GetType(), this.instruction, ex.ToString());
    //            }
    //        }
    //        finally
    //        {
    //            conn.Close();
    //        }
    //        return pdfDocs;
    //    }

    //    private string SaveFile(byte[] contents, string folderPath, string fileName)
    //    {
    //        string tgtFilePath = Path.Combine(folderPath, fileName);
    //        if (!File.Exists(tgtFilePath))
    //        {
    //            BinaryWriter writer = new BinaryWriter(File.OpenWrite(tgtFilePath));
    //            writer.Write(contents, 0, contents.Length);
    //            writer.Flush();
    //            writer.Close();
    //        }
    //        return tgtFilePath;
    //    }
    //    #endregion
    //}
}
